package com.cht.commonUtil.widgets;

import static java.lang.System.out;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Scanner;

import javax.activation.DataHandler;
import javax.activation.FileDataSource;
import javax.mail.Message.RecipientType;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.UsernamePasswordCredentials;
import org.apache.commons.httpclient.auth.AuthScope;
import org.apache.commons.httpclient.methods.PostMethod;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;
import org.jboss.as.cli.CommandContext;
import org.jboss.as.cli.CommandContextFactory;
import org.tmatesoft.svn.core.ISVNDirEntryHandler;
import org.tmatesoft.svn.core.SVNCommitInfo;
import org.tmatesoft.svn.core.SVNDepth;
import org.tmatesoft.svn.core.SVNDirEntry;
import org.tmatesoft.svn.core.SVNException;
import org.tmatesoft.svn.core.SVNLogEntry;
import org.tmatesoft.svn.core.SVNNodeKind;
import org.tmatesoft.svn.core.SVNRevisionProperty;
import org.tmatesoft.svn.core.SVNURL;
import org.tmatesoft.svn.core.auth.ISVNAuthenticationManager;
import org.tmatesoft.svn.core.internal.io.dav.DAVRepositoryFactory;
import org.tmatesoft.svn.core.internal.util.SVNEncodingUtil;
import org.tmatesoft.svn.core.internal.wc.DefaultSVNOptions;
import org.tmatesoft.svn.core.io.ISVNEditor;
import org.tmatesoft.svn.core.io.SVNRepository;
import org.tmatesoft.svn.core.io.SVNRepositoryFactory;
import org.tmatesoft.svn.core.io.diff.SVNDeltaGenerator;
import org.tmatesoft.svn.core.wc.SVNClientManager;
import org.tmatesoft.svn.core.wc.SVNLogClient;
import org.tmatesoft.svn.core.wc.SVNRevision;
import org.tmatesoft.svn.core.wc.SVNWCUtil;

import com.cht.commonUtil.enums.ReqStatus;
import com.cht.commonUtil.enums.Roles;
import com.cht.commonUtil.enums.ToDoType;
import com.cht.commonUtil.jenkins.URLConnectionUtil;
import com.cht.commonUtil.structure.FileStr;
import com.cht.commonUtil.structure.L1S1;
import com.cht.commonUtil.structure.QDPS;
import com.cht.commonUtil.structure.Req;
import com.cht.commonUtil.structure.ResultPack;
import com.cht.commonUtil.structure.S2;
import com.cht.commonUtil.structure.S3;
import com.cht.commonUtil.structure.S5;
import com.cht.commonUtil.structure.S8;
import com.cht.commonUtil.structure.SquadParis;
import com.cht.commonUtil.webInit.SP;
import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelExec;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;

/**
 * @author Arthur
 *
 */
public class Widgets {

	final private static String JNDINAME = "java:jboss/datasources/DPS";
	final public static String SYSID = "sys01";
	final public static String TOKENONE = "PPPP";
	final public static String splitTokenONE = "UUUU";
	
	String res = "[";
	
	//取得指定字串(strkey)，在strKeywords中出現的位置
	public static List<Integer> getIdxArr(String strKeywords, String strkey) {

		List<Integer> res = new ArrayList<Integer>();
		for(int pivot = strKeywords.indexOf(strkey);pivot!=-1;pivot = strKeywords.indexOf(strkey,pivot+1 ) ) {
			res.add(pivot);
		}
		return res;
	}
	
	public static String getSVNcomment(String url,String usr,String pwd,long reversion ) {
		String res = "";	//欲回傳的comment message
		SVNRepository repo = null;
		try {
			repo = SVNRepositoryFactory.create( SVNURL.parseURIEncoded(SVNEncodingUtil.uriEncode(url)) );
			ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr, pwd);
			repo.setAuthenticationManager(authManager);
			res = repo.getRevisionPropertyValue(reversion,SVNRevisionProperty.LOG).getString(); 
			if( res == null ) { res=""; }
		} catch(Exception e) {
			errLog(e);
		} finally {
			if(repo!=null) { repo.closeSession(); }
		}
		return res;
	}
	
	public static ArrayList<QDPS> getQDR4Paris(int localSystemId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		
		ArrayList<QDPS> qdps = new ArrayList<QDPS>();
		
		
		try {
			conn = getConn();
			
			// 查詢 fDepdetail 確認該次部署到底部署了那些Squad(因為Squad的設定可能會變，所以不能直接讀當前的squad)
			//查詢所有部署記錄
			String sql = "SELECT t1.depmainSN,t3.deliverySN,t1.isUrgent,t5.envId,t5.envName,t4.empName,t1.createdDateTime,t2.deplmainSN FROM depmain t1,deplmain t2, deltask t3,emps t4,env t5 WHERE t4.sysSN=? AND t1.subsysId=t5.subsysId AND t1.envId=t5.envId AND t1.creator=t4.empId AND t1.deplmainSN=t2.deplmainSN AND t2.deplmainSN=t3.deplmainSN ORDER BY depmainSN DESC";			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, localSystemId);
			rs = ps.executeQuery();
			while( rs.next() ) {
				QDPS qdp = new QDPS(); 
				int rowsnum = 0;
				ArrayList<SquadParis> sps = new ArrayList<SquadParis>();	//這次部署牽涉那些Server Group
				qdp.setSps(sps);
				ArrayList<FileStr> files = new ArrayList<FileStr>();	//這次部署那些檔案
				ArrayList<Req> reqs = new ArrayList<Req>();			//包含那些需求
				
				sql = "SELECT DISTINCT t1.reqSID,t1.reqDESC FROM reqs t1,mpreqfiles t2 WHERE t1.reqId=t2.reqSN AND t2.deplmainSN=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, rs.getInt("deplmainSN") );
				rs2 = ps.executeQuery();
				while( rs2.next() ) {
					Req req = new Req();
					req.setReqSID( rs2.getString("reqSID") );
					req.setReqDesc( rs2.getString("reqDesc") );
					reqs.add(req);
				} 
				qdp.setReqs(reqs);
				qdp.setDepmainSN( rs.getInt("depmainSN") );
				qdp.setDeliverySN( rs.getInt("deliverySN") );
				qdp.setUrgent( "t".equalsIgnoreCase( rs.getString("isUrgent") ) );
				qdp.setEnvId( rs.getString("envId") );
				qdp.setEnvName( rs.getString("envName") );
				qdp.setEmpName( rs.getString("empName") );
				qdp.setbDT( changeTimeFormatUI( rs.getTimestamp("createdDateTime") ) );
				if( ps!=null ) { ps.close();}
				if( rs2!=null ) { rs2.close();}
				
				sql = "SELECT DISTINCT t2.localFile,t2.localVer,t2.fileSizeInByte FROM depmain t1,depldetail t2 WHERE t1.depmainSN=? AND t1.deplmainSN=t2.deplmainSN ORDER BY localFile DESC";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, qdp.getDepmainSN() );
				rs2 = ps.executeQuery();
				while( rs2.next() ) {
					FileStr file = new FileStr();
					file.setFileName( rs2.getString("localFile") );		//檔名
					file.setLocalVer( rs2.getInt("localVer") );			//版次
					file.setFileSize( rs2.getInt("fileSizeInByte") );	//檔案大小
					files.add(file);
				}
				qdp.setFiles(files);
				
				//查詢在該次部署中，部署了那些Server Group
				sql = "SELECT DISTINCT squadId FROM fdepDetail WHERE depmainSN=? AND squadId IS NOT NULL";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, qdp.getDepmainSN() );
				rs2 = ps.executeQuery();
				while( rs2.next() ) {
					SquadParis sp = new SquadParis();
					String squadId = rs2.getString("squadId");
					sp.setSquadId( squadId );
					
					ArrayList<FileStr> reloadFiles = new ArrayList<FileStr>();	//這次重新上下的檔案
					
					//重新上下<下>
					sql = "SELECT fDepActTypeId,depFN,bDT,eDT,depResult FROM fdepdetail WHERE depmainSN=? AND (fDepActTypeId=8 OR fDepActTypeId=9) AND squadId=? ORDER BY fdepdetailId";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, qdp.getDepmainSN() );
					ps.setString(2, squadId );
					rs3 = ps.executeQuery();
					while( rs3.next() ) {
						FileStr file = new FileStr();
						file.setFileName( rs3.getString("depFN") );		//檔名
						S8 s8 = new S8();
						if( rs3.getInt("fDepActTypeId") == 8 ) {
							s8.setS3( rs3.getString("depResult") );
							s8.setS1( s8.getS3().indexOf("{\n    \"outcome\" => \"success\"")!=0?"失敗":"成功");
							s8.setS5( getTime( rs3.getTimestamp("bDT") ) );
							s8.setS7( getTime( rs3.getTimestamp("eDT") ) );
						} else {
							s8.setS4( rs3.getString("depResult") );
							s8.setS2( s8.getS4().indexOf("{\n    \"outcome\" => \"success\"")==0?"成功":"失敗");
							s8.setS6( getTime( rs3.getTimestamp("bDT") ) );
							s8.setS8( getTime( rs3.getTimestamp("eDT") ) );
						}
						file.setDepRes(s8);
						reloadFiles.add(file);
					}
					ArrayList<FileStr> filesInSP = new ArrayList<FileStr>();	//這次部署那些檔案
					for(int i=0;i<files.size();i++) {
						FileStr file = new FileStr();
						file.setFileName( files.get(i).getFileName() );		//檔名
						file.setLocalVer( files.get(i).getLocalVer() );		//版次
						file.setFileSize( files.get(i).getFileSize() );		//檔案大小
						filesInSP.add(file);
					}
					for (int i=0;i<filesInSP.size();i++) {
						S8 s8 = new S8();
						
						sql = "SELECT bDT,eDT,depResult FROM fdepdetail WHERE depmainSN=? AND fDepActTypeId=5 AND depFN=? AND squadId=?";
						ps = conn.prepareStatement(sql);
						ps.setInt(1, qdp.getDepmainSN() );
						ps.setString(2, filesInSP.get(i).getFileName() );
						ps.setString(3, squadId );
						rs3 = ps.executeQuery();
						if ( rs3.next() ) {	//表示這次部署，這個Server Group上，有對這支檔案進行下線
							s8.setS3( stringFilter( rs3.getString("depResult") ) );
							s8.setS1( s8.getS3().indexOf("{\n    \"outcome\" => \"success\"")!=0?"失敗":"成功");
							s8.setS5( getTime( rs3.getTimestamp("bDT") ) );
							s8.setS7( getTime( rs3.getTimestamp("eDT") ) );
						} else {
							s8.setS1("未執行");
						}
						if( ps!=null ) { ps.close();}
						if( rs3!=null ) { rs3.close();}
						
						sql = "SELECT bDT,eDT,depResult FROM fdepdetail WHERE depmainSN=? AND fDepActTypeId=6 AND depFN=? AND squadId=?";
						ps = conn.prepareStatement(sql);
						ps.setInt(1, qdp.getDepmainSN() );
						ps.setString(2, filesInSP.get(i).getFileName() );
						ps.setString(3, squadId );
						rs3 = ps.executeQuery();
						if ( rs3.next() ) {	//表示這次部署，這個Server Group上，有對這支檔案進行部署
							s8.setS4( stringFilter( rs3.getString("depResult") ) );
							s8.setS2( s8.getS4().indexOf("{\n    \"outcome\" => \"success\"")==0?"成功":"失敗");
							s8.setS6( getTime( rs3.getTimestamp("bDT") ) );
							s8.setS8( getTime( rs3.getTimestamp("eDT") ) );
						} else {
							s8.setS2("未執行");
						}
						filesInSP.get(i).setDepRes( s8 );
					}
					sp.setFiles(filesInSP);	//ServerGroup下的檔案已處理完成
					sp.setReloadFiles(reloadFiles);

					ArrayList<S5> hts = new ArrayList<S5>();	//s1 ip名稱,s2關閉Apache的執行狀況,s3開啟Apache的執行狀況
					
					sql = "SELECT DISTINCT hostIP FROM fdepdetail WHERE depmainSN=? AND hostIP IS NOT NULL AND squadId=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, qdp.getDepmainSN() );
					ps.setString(2, squadId );
					rs3 = ps.executeQuery();
					while( rs3.next() ) {
						S5 ht = new S5();
						ht.setS1( rs3.getString("hostIP") );
						hts.add(ht);
					}
					sp.setHts(hts);					
					
					sql = "SELECT fdepActTypeId,hostIP,depResult FROM fdepDetail WHERE depmainSN=? AND hostIP IS NOT NULL AND fDepActTypeId IN (3,7) AND squadId=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, qdp.getDepmainSN() );
					ps.setString(2, squadId );
					rs3 = ps.executeQuery();
					while( rs3.next() ) {
						if ( rs3.getInt("fdepActTypeId") == 3 ) {	//s2記錄關閉httpd Service成功否，s4是原始執行記錄
							for(int i=0;i<sp.getHts().size();i++) {	
								if( sp.getHts().get(i).getS1().equalsIgnoreCase(rs3.getString("hostIP")) ) {
									//s2記錄關閉的執行結果
									sp.getHts().get(i).setS2( rs3.getString("depResult").contains("httpd is stopped")?"成功":"失敗" );
									sp.getHts().get(i).setS4( rs3.getString("depResult") );
								}
							}
						} else {									//s3記錄開啟httpd Service成功否，s5是原始執行記錄
							for(int i=0;i<sp.getHts().size();i++) {
								if( sp.getHts().get(i).getS1().equalsIgnoreCase(rs3.getString("hostIP")) ) {
									//s3記錄開啟的執行結果
									sp.getHts().get(i).setS3( rs3.getString("depResult").contains("httpd (pid")?"成功":"失敗" );
									sp.getHts().get(i).setS5( rs3.getString("depResult") );
								}
							}
						}
					}
					rowsnum += (5+sp.getHts().size()*2+sp.getFiles().size()*2+sp.getReloadFiles().size() );
					qdp.getSps().add(sp);
				}
				qdp.setRowspan(rowsnum);
				qdps.add(qdp);
			}
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( rs2 !=null ) { rs2.close(); }
				if ( rs3 !=null ) { rs3.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }
			} catch (Exception e) { e.printStackTrace(); }
		}
		return qdps;
	}
	
	/**
	 * 將傳出的字串，若為null或為字串"null"，則回傳空字串""
	 * @param str
	 * @return<p>	原字串或空字串
	 * Commented on:2014/3/14 上午9:07:30<br>
	 * Created by:  Arthur
	 */
	public static String stringFilter(String str) {
		String res=null;
		
		if ( isReqExist(str) ) {
			res = str;
		}
		if ( res == null || "null".equalsIgnoreCase(res) ) {
			res = "";	//如果沒有值，回傳空字串不要會傳null
		}
		
		return res;
	}
	
	/**
	 * 取得指定Datetime 的時間
	 * @param dt
	 * @return<p>
	 * Commented on:2014/3/20 下午8:02:27<br>
	 * Created by:  Arthur
	 */
	private static String getTime(Date dt) {
		String result ="";
		if( dt != null ) {
			result = new java.text.SimpleDateFormat("HH:mm:ss").format(dt); 
		}
		
		return result;
	}
	
	/*
	 * 0 尚未測試	
	 * 2 主機不能連
	 * 3 帳密不通
	 * 4 連線正常
	 * 5不存在指定路徑
	 * */
	public static int chkFTPconnection(String url,String usr,String pwd,String path) throws JSchException {
		int res =0;
		
		JSch jsch = new JSch();
        JSch.setConfig("StrictHostKeyChecking", "no");
        com.jcraft.jsch.Session session = null;
        ChannelSftp sftp = null;
		
        
        try {
        	session = jsch.getSession(usr, url);
    		session.setPassword( pwd );
    		try {
    			session.connect();
    			sftp = (ChannelSftp) session.openChannel("sftp");
    			sftp.connect();
    			sftp.cd("/");
    			cwdButNotmkdir(sftp,path);
    			res = 4;
    		} catch (Exception e) {
    			if ( "Auth fail".equalsIgnoreCase(e.getMessage()) ) {
    				res=3;	//帳密不通	
    			} else if(e.getMessage().contains("Connection timed out: connect") ) {
    				res=2;	//主機不能連
    			} else if ( "No such file".equalsIgnoreCase(e.getMessage()) ) {
    				res=5;	//不存在指定路徑
    			} else {
    				errLog(e);
    			}
    		}
        } catch (Exception e) {
        	res=2;
        	errLog(e);
        } finally {
        	if ( sftp != null ) { if ( sftp.isConnected() ) { sftp.disconnect(); } }
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect(); } }
        }
        
        return res;
	}
	
	/*
	 * 0 尚未測試	
	 * 2 主機不能連
	 * 3 帳密不通
	 * 4 連線正常
	 * 5不存在指定路徑
	 * 6檔案不存在
	 * */
	public static int isFileOnFTP( String url,String usr,String pwd,String path,String fileName) throws JSchException {
		int res=0;
		
		JSch jsch = new JSch();
        JSch.setConfig("StrictHostKeyChecking", "no");
        com.jcraft.jsch.Session session = null;
        ChannelSftp sftp = null;
		
        try {
        	session = jsch.getSession(usr, url);
    		session.setPassword( pwd );
    		try {
    			session.connect();
    			sftp = (ChannelSftp) session.openChannel("sftp");
    			sftp.connect();
    			sftp.cd("/");
    			System.out.println("檔案處理目前的位址："+sftp.pwd());
    			System.out.println("檔案處理要去的位址："+path);
    			cwdButNotmkdir(sftp,path);
    			res = 4;
    		} catch (Exception e) {
    			if ( "Auth fail".equalsIgnoreCase(e.getMessage()) ) {
    				res=3;	//帳密不通	
    			} else if(e.getMessage().contains("Connection timed out: connect") ) {
    				res=2;	//主機不能連
    			} else if ( "No such file".equalsIgnoreCase(e.getMessage()) ) {
    				res=5;	//不存在指定路徑
    			}
    		}
    		
    		if ( res ==4 ) {
    			try {
    				sftp.get(fileName);
    				System.out.println("檔案"+fileName+"是存在的");
    			} catch (Exception e) {
    				if ( "No such file".equalsIgnoreCase(e.getMessage()) ) {
    					res = 6; //檔案不存在
    				}
    			}
    		}
        } finally {
        	if ( sftp != null ) { if ( sftp.isConnected() ) { sftp.disconnect(); } }
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect(); } }
        }
		
		
		return res;
	}
	
	private static void cwdButNotmkdir (ChannelSftp sftp, String path) throws SftpException {
		
		String[] hie  = path.split("/");
		for(int i=0; i<hie.length;i++) {
			if ("".equalsIgnoreCase(hie[i])) {continue;}
			sftp.cd(hie[i]);
			System.out.println(sftp.pwd());
		}
	}
	
	public static ArrayList<S3> getSubsysList(String empId, String pageName,int localSystemId ) throws Exception {
		String sql = "SELECT COUNT(*) FROM mpemprolesubsys WHERE empId=? AND sysSN=? AND roleId=1"; 
		ArrayList<S3> subsyses = new ArrayList<S3>(); 	//因為subsyses 中有四個s
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, empId);
			ps.setInt(2, localSystemId);
			rs = ps.executeQuery();
			rs.next();
			boolean isSuperAdmin = (rs.getInt(1)==1);
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( isSuperAdmin ) {
				sql = "SELECT subsysId,subsysDesc,isReqDescNeed FROM subsyses";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
			} else {
				sql = "SELECT t1.subsysId,t1.subsysDesc,t1.isReqDescNeed FROM subsyses t1,pageList t2,mprolepagelist t3,mpemprolesubsys t4 WHERE t4.roleId=t3.roleId AND t1.subsysId=t4.subsysId AND t4.empId=? AND t3.pageId=t2.pageId AND t2.pageUrl=? AND t4.sysSN=? ORDER BY t1.subsysId";
				ps = conn.prepareStatement(sql);
				ps.setString(1, empId);
				ps.setString(2, pageName);
				ps.setInt(3, localSystemId);
				rs = ps.executeQuery();
			}
			while ( rs.next() ) {
				S3 subsys = new S3();
				subsys.setS1( rs.getString("subsysId") );
				subsys.setS2( rs.getString("subsysDesc") );
				subsys.setS3( rs.getString("isReqDescNeed") );
				subsyses.add(subsys);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return subsyses;		
	}
	
	
	/*
	 * 0 尚未測試	
	 * 1 帳密不通
	 * 2 主機不能連
	 * 99 連線正常
	 * */
	public static int chkHostConnection(String url,String usr,String pwd) throws JSchException {
		int res =0;
		
		JSch jsch = new JSch();
        JSch.setConfig("StrictHostKeyChecking", "no");
        com.jcraft.jsch.Session session = null;
		
        try {
        	session = jsch.getSession(usr, url, 22);
        	session.setPassword(pwd);
    		try {
    			session.connect();
    			res = 99;
    		} catch (Exception e) {
    			if ( "Auth cancel".equalsIgnoreCase(e.getMessage()) || e.getMessage().contains("Auth fail") ) {
    				res=1;	//帳密不通	
    			} else if(e.getMessage().contains("java.net.UnknownHostException") || e.getMessage().contains("Connection timed out: connect") ) {
    				res=2;	//主機不能連
    			}
    			System.out.println(e.toString());
    		}
        } finally {
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect(); } }
        }
        
        return res;
	}

	public static String chkServiceStatus(String Url,String Id,String Pwd) throws Exception {
		String res = "";
		InputStream ins = null;
		Scanner scannerA = null;
		Scanner scannerB = null;
		Channel channel = null;
		Session session = null;
		try {
			JSch jsch = new JSch();
			Properties config = new java.util.Properties();
			
			config.put("StrictHostKeyChecking", "no");
			
			session = jsch.getSession(Id, Url, 22);
			session.setConfig(config);
			session.setPassword(Pwd);
			session.connect();
			
			String cmd = "";
			
			if ("root".equalsIgnoreCase(Id)) {
				cmd = "service httpd status";
			} else {
				cmd = "sudo service httpd status";
			}
			
			channel = session.openChannel("exec");
			((ChannelExec) channel).setCommand( cmd );

			channel.setInputStream(null);

			((ChannelExec) channel).setErrStream(System.err);

			ins = channel.getInputStream();

			channel.connect();

			scannerA = new Scanner(ins);
			scannerB = scannerA.useDelimiter("\\A");
		    res =scannerB.hasNext() ? scannerB.next() : ""; 
		    
			channel.disconnect();
			session.disconnect();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if ( ins != null) { ins.close(); }
			if ( scannerA != null) { scannerA.close(); }
			if ( scannerB != null) { scannerB.close(); }
			if ( channel != null ) { if ( channel.isConnected() ) { channel.disconnect(); } }
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect();} }
		}
		
		return res;
	}
	/**	取得指定部署執行下的部署細項號
	 * @param depmainSN
	 * @return
	 */
	public static int getfDepdetailid(int depmainSN) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int fdepdetailid =0;
		try {
			conn = getConn();
			String sql = "SELECT COUNT(*) FROM fdepdetail WHERE depmainSN=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, depmainSN);
			rs = ps.executeQuery();
			rs.next();
			fdepdetailid = rs.getInt(1)+1;
		} catch (Exception x) {
			errLog(x);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }	
			} catch (Exception e) { e.printStackTrace(); }
		}
		return fdepdetailid;
	}
	
	public static int genToDoList(String subsysId,String masterId,String empId,int dataType, int roleId ) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int toDoListId =0;
		try {
			conn = getConn();
			String sql="INSERT INTO todolist (subsysId,dataType,masterId,roleId,cDT,creatorId) VALUES (?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS );
			ps.setString(1, subsysId );
			ps.setInt(2, dataType);				//主檔類型，1表示這個ID是來自變更申請
			ps.setString(3, masterId);			//主檔ID
			ps.setInt(4, roleId);				//角色ID
			ps.setTimestamp(5, cDT());
			ps.setString(6, empId);
			ps.executeUpdate();
			
			rs = ps.getGeneratedKeys();
			rs.next();
			toDoListId = rs.getInt(1);
			System.out.println("回傳時的toDoListId:"+toDoListId);
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }	
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return toDoListId;
	}
	
	public static ArrayList<String> getSVNConn4Delivery(int fromSysNo,int targetSystemId) {
		ArrayList<String> connInfo = new ArrayList<String>();
		System.out.println("從："+fromSysNo+",去："+targetSystemId);
		
		String sql = "SELECT loginURL,id,pwd FROM mpsyssvn t1,svnlogin t2 WHERE t1.fromSysSN=? AND t1.tarSysSN=? AND t1.svnLoginSN=t2.svnLoginSN";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, fromSysNo);
			ps.setInt(2, targetSystemId);
			rs = ps.executeQuery();
			rs.next();
			
			connInfo.add( rs.getString("loginURL") );
			connInfo.add( rs.getString("id") );
			connInfo.add( rs.getString("pwd") );
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return connInfo;
	}
	
	//取得至指定系統的 資料庫連線 
	public static Connection getTarConnection(int targetSystemId) throws SQLException {
		Connection conn = null;
		Connection tarConn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT t1.tarSysSN,t2.ip,t2.port,t2.id,t2.pwd,t2.dbName,t3.strProtocol FROM mpsysdb t1,dblogin t2,dbtype t3 WHERE t2.DBTypeSN=t3.DBTypeSN AND t1.dbLoginSN=t2.dbLoginSN AND t1.tarSysSN =?");
			ps.setInt(1, targetSystemId);
			rs = ps.executeQuery();
			rs.next();
				
			Class.forName("com.mysql.jdbc.Driver");
			String url = rs.getString("strProtocol")+"://"+rs.getString("ip")+":"+rs.getInt("port")+"/"+rs.getString("dbName");
			tarConn = DriverManager.getConnection( url,rs.getString("id"),rs.getString("pwd") );
			
		} catch (Exception e) {
			System.out.println(errProcess(e));
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		return tarConn;
	}
	
	/**
	 * 查詢這封信，它的收件人有誰
	 * @param mailId
	 * @param recipientTypeId
	 * @param subsysId
	 * @param envId
	 * @param systemId
	 * @return
	 * @throws SQLException<p>
	 * Commented on:2014/6/4 上午10:26:30<br>
	 * Created by:  Arthur
	 */
	public static ArrayList<String[]> getRecipient(int mailId,int recipientTypeId,String subsysId) throws SQLException {
		ArrayList<String[]> recipients = new ArrayList<String[]>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs= null;
		String sql = ""; 
		
		System.out.println("子系統:"+subsysId);
		System.out.println("mailId:"+mailId);
		System.out.println("recipientTypeId:"+recipientTypeId);
		List<Integer> syses = new ArrayList<Integer>();
		try {
			conn = getConn();
			sql ="SELECT sysSN FROM envsystemlist";
			ps = conn.prepareStatement( sql );
			rs = ps.executeQuery();
			while( rs.next() ) {
				syses.add( rs.getInt(1) );
			}
			if ( rs!=null ) {rs.close();}
			if ( ps!=null ) {ps.close();}
			
			
			String tmp = "(SELECT t2.roleId FROM mpmailtyperolesubsyssystem t1,roles t2,subsyses t3,maillisttype t4, envsystemlist t5 WHERE t1.systemId=t5.sysSN AND t1.mailTypeId=t4.mailId AND t1.roleId=t2.roleId AND t1.subsysId=t3.subsysId AND t1.subsysId='"+subsysId+"' AND t1.mailTypeId="+mailId+" AND t1.recipientTypeId="+recipientTypeId+" AND t1.systemId=? )";
			
			sql = "SELECT t8.empEmail,t8.empName FROM mpemprolesubsys t7,emps t8 WHERE t7.sysSN=t8.sysSN AND t7.empId=t8.empId AND t7.subsysId=? AND t7.roleId IN "+tmp+" AND t7.sysSN=?";
			ps = conn.prepareStatement( sql );
			ps.setString(1, subsysId);
			for( int j=0;j<syses.size();j++ ) {
				ps.setInt(2, syses.get(j));
				ps.setInt(3, syses.get(j));
				System.out.println("系統ID:"+syses.get(j));
				rs = ps.executeQuery();
				while ( rs.next() ) {
					String[] recipient = new String[2];
					recipient[0] = rs.getString("empName");
					recipient[1] = rs.getString("empEmail");
					recipients.add( recipient );
				}
			}
		} catch (Exception e) {
			errLog(e);
		} finally {
			if ( rs!=null ) {rs.close();}
			if ( ps!=null ) {ps.close();}
			if ( conn!=null ) {conn.close();}
		}
		return recipients;
	}
	
	public static ArrayList<String[]> getRecipient(int mailId,int recipientTypeId,String subsysId,String envId) throws SQLException {
		ArrayList<String[]> recipients = new ArrayList<String[]>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs= null;
		String sql = ""; 
		
		System.out.println("mailId:"+mailId);
		System.out.println("recipientTypeId:"+recipientTypeId);
		System.out.println("subsysId:"+subsysId);
		System.out.println("envId:"+envId);
		
		try {
			conn = getConn();
			sql = "SELECT t8.empEmail,t8.empName FROM mpemprolesubsysenv t7,emps t8 WHERE t7.sysSN=t8.sysSN AND t7.empId=t8.empId AND t7.subsysId=? AND t7.envId=? AND t7.roleId IN ( SELECT t2.roleId FROM mpmailtyperolesubsyssystem t1,roles t2,subsyses t3,maillisttype t4, envsystemlist t5 WHERE t1.systemId=t5.sysSN AND t1.mailTypeId=t4.mailId AND t1.roleId=t2.roleId AND t1.subsysId=t3.subsysId AND t1.subsysId=? AND t1.mailTypeId=? AND t1.recipientTypeId=? )";
			ps = conn.prepareStatement( sql );
			int i=1;
			ps.setString(i++, subsysId);
			ps.setString(i++, envId);
			ps.setString(i++, subsysId);
			ps.setInt(i++, mailId);
			ps.setInt(i++, recipientTypeId);
			rs = ps.executeQuery();
			while ( rs.next() ) {
				String[] recipient = new String[2];
				recipient[0] = rs.getString("empName");
				recipient[1] = rs.getString("empEmail");
				recipients.add( recipient );
			}
			
		} catch (Exception e) {
			errLog(e);
		} finally {
			if ( rs!=null ) {rs.close();}
			if ( ps!=null ) {ps.close();}
			if ( conn!=null ) {conn.close();}
		}
		return recipients;
	}
	
	/**修改交付事件，目前的狀態
	 * 
	 * @param deliverySN
	 * @param statusSN
	 * @param targetSystemId<p>
	 * Commented on:2014/3/12 上午9:51:19<br>
	 * Created by:  Arthur
	 */
	public static void updateDeltaskStatus(int deliverySN,int statusSN,Connection conn ) {
		String sql = "UPDATE deltask SET statusSN=? WHERE deliverySN=?";
		
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, statusSN);
			ps.setInt(2, deliverySN);
			ps.execute();
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }
			} catch (Exception e) { e.printStackTrace(); }
		}
	}
	
	//這個檔案必須包含了 repo下，其所在路徑加檔名
	public static long getSVNfileSize(String url,String usr,String pwd,String fileName, long ver) throws Exception {
		DAVRepositoryFactory.setup();
		SVNRepository repo = SVNRepositoryFactory.create( SVNURL.parseURIEncoded(SVNEncodingUtil.uriEncode(url)) );
		ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr, pwd);
		repo.setAuthenticationManager(authManager);
		SVNDirEntry entry = repo.info(fileName, ver);
		repo.closeSession();
		return entry.getSize();
	}
	
	public static int getCurrentSN(String table) {
		String sql = "UPDATE envtable_seq SET seqcurrent = (seqcurrent + 1) WHERE seqname=?";
		int res = 0;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, table);
			ps.execute();
			if ( ps !=null ) {ps.close();}
			
			sql = "SELECT seqcurrent FROM envtable_seq WHERE seqname=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, table);
			rs = ps.executeQuery();
			rs.next();
			res = rs.getInt(1);
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return res;
	}
	
	/**	檢查這個需求是否已經存在
	 * 0 尚未測試
	 * 1  同樣的組合已經存在
	 * 2 reqSID已存在，但需求描述不是所輸入的
	 * 3  不存在
	 * @param reqSID
	 * @param reqDesc
	 * @return
	 */
	public static int isReqExisted(String reqSID, String reqDesc) {
		int res = 0;
		String sql = "SELECT COUNT(*) FROM reqs WHERE reqSID=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, reqSID);
			rs = ps.executeQuery();
			rs.next();
			int count = rs.getInt(1);
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			
			if ( count != 0 ) {	//這個ID已經被用了
				sql = "SELECT COUNT(*) FROM reqs WHERE reqSID=? AND reqDesc=?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, reqSID);
				ps.setString(2, reqDesc);
				rs = ps.executeQuery();
				rs.next();
				if ( rs.getInt(1) != 0 ) {	//同樣的組合已經存在
					res =1;
				} else { //reqSID已存在，但需求描述不是所輸入的
					res=2;
				}
			} else {
				res =3;
			}
		} catch (Exception x) {
			errLog(x);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return res;
	}
	
	
	
	//從資料庫查詢已有的部署版次記錄
	public static List<L1S1> getDeployedVersion(String svnUrl,String fileName) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<L1S1> exeVers = new ArrayList<L1S1>();
		
		String sql = "SELECT DISTINCT t1.fileVer FROM depmemphisdetail t1,depMemphis t2,svnexerepos t3 WHERE t2.svnURL=t3.url AND t2.cDT>t3.lastDT AND t1.depMemphisSN=t2.depMemphisSN AND t1.isDep='t' AND t1.isSuccess='t' AND t1.fileName=? AND t2.svnURL=? AND t1.squadId='testEnvServerGroup' ORDER BY t1.fileVer DESC";
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, fileName);
			ps.setString(2, svnUrl);
			rs = ps.executeQuery();
			while( rs.next() ) {
				L1S1 is = new L1S1();
				is.setL1( rs.getLong(1) );
				exeVers.add( is ); 
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) { rs.close(); }
			if ( ps !=null ) { ps.close(); }
			if ( conn !=null ) { conn.close(); }
		}
		
		return exeVers;
	}
	
	public static ArrayList<L1S1> chkFileRevisionAndComment(boolean isMR,String url,String usr,String pwd,String relativePath,String fileName,boolean isStrict) throws Exception {
		
		ArrayList<L1S1> verSets = new ArrayList<L1S1>();
		ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr,pwd);
		
		int baseVer = 0;
		/*if( isStrict ) {
			baseVer = getMAXfileVer(isMR,relativePath,fileName );
		}*/
		SVNRepository repo = null;
		
		String targetFilePath = fileName;
		if( !"".equalsIgnoreCase(relativePath)) {
			targetFilePath = "/"+relativePath+"/"+fileName;
		}
		
		try {
			repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
			repo.setAuthenticationManager(authManager);
			long startRevision = 0;
			long endRevision = -1;
			
			Collection<?> logEntries = repo.log( new String[] { targetFilePath } , null, startRevision , endRevision , true , true );
			
			for ( Iterator entries = logEntries.iterator(); entries.hasNext(); ) {
				SVNLogEntry logEntry = ( SVNLogEntry ) entries.next();
				if ( logEntry.getRevision() >= baseVer ) {
					L1S1 vs = new L1S1();
	        		vs.setL1( logEntry.getRevision() );
	        		vs.setS1( logEntry.getMessage() );
	        		verSets.add( vs );	
	    		}
			}
	       
	        Collections.reverse(verSets);
		} catch(Exception e) {
			throw e;
		} finally {
			if(repo!=null) {repo.closeSession(); }	
		}
		return verSets;
	}

	public static String[] getAP4SubsysConnInfo(String subsysId, int apType) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String[] aps = new String[4];
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT apIP,apPORT,apUSR,apPWD FROM MPsubsys2APserver WHERE subsysId=? AND apType=?");
			ps.setString(1, subsysId);
			ps.setInt(2, apType);
			rs = ps.executeQuery();
			rs.next();
			aps[0] = rs.getString("apIP");
			aps[1] = String.valueOf( rs.getInt("apPORT") );
			aps[2] = rs.getString("apUSR");
			aps[3] = rs.getString("apPWD");
			
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return aps;
	}
	
	public static void sendMailMain(List<String[]> to,List<String[]> cc, List<String[]> bcc, String subject,String msgBody) throws MessagingException,UnsupportedEncodingException {
		String fromAddr = "fakeEmailAddr@cht.com.tw"; // 公司內的Server一定要一個fromAddr，只好就捏造一個
		String fromAddrName = "程式上線更版系統";

		Properties prop = new Properties();
		prop.put(SP.mailServer, SP.mailServerIP);

		javax.mail.Session session = javax.mail.Session.getDefaultInstance(prop, null);
		MimeMessage msg = new MimeMessage(session);

		msg.setSubject(subject);
		InternetAddress from = new InternetAddress(fromAddr);
		from.setPersonal(fromAddrName, SP.mailLocale);
		msg.setFrom(from);

		InternetAddress ia = new InternetAddress();

		String ml = "";

		// 正本收件人
		for (int i = 0; i < to.size(); i++) {
			if (!ml.contains(to.get(i)[1])) {
				System.out.println("正本收件人:" + to.get(i)[0]);
				ia.setPersonal(to.get(i)[0], SP.mailLocale);
				ia.setAddress(to.get(i)[1]);
				msg.addRecipient(RecipientType.TO, ia);
				ml += to.get(i)[1] + "&&";
			}
		}
		// 副本收件人
		for (int i = 0; i < cc.size(); i++) {
			if (!ml.contains(cc.get(i)[1])) {
				System.out.println("副本收件人:" + cc.get(i)[0]);
				ia.setPersonal(cc.get(i)[0], SP.mailLocale);
				ia.setAddress(cc.get(i)[1]);
				msg.addRecipient(RecipientType.CC, ia);
				ml += cc.get(i)[1] + "&&";
			}
		}

		// 密件副本收件人
		for (int i = 0; i < bcc.size(); i++) {
			if (!ml.contains(bcc.get(i)[1])) {
				ia.setPersonal(bcc.get(i)[0], SP.mailLocale);
				ia.setAddress(bcc.get(i)[1]);
				msg.addRecipient(RecipientType.BCC, ia);
				ml += bcc.get(i)[1] + "&&";
			}
		}
		
		
		// 確認存在收信者，再寄信
		if (msg.getAllRecipients() != null &&msg.getAllRecipients().length > 0) {
			Multipart mp = new MimeMultipart();
			MimeBodyPart mbp1 = new MimeBodyPart();
			mbp1.setContent(msgBody, "text/html;charset=UTF-8");
			mp.addBodyPart(mbp1);
			msg.setContent(mp);
			Transport.send(msg);
			System.out.println("收件人清單長度："+msg.getAllRecipients().length);
		} else {
			System.out.println("沒有找到任何收件人耶");
		}
	}

	
	public static void sendMailMain(List<String[]> to,List<String[]> cc, List<String[]> bcc, String subject,String msgBody,File file) throws Exception {
		String fromAddr = "fakeEmailAddr@cht.com.tw"; // 公司內的Server一定要一個fromAddr，只好就捏造一個
		String fromAddrName = "程式上線更版系統";

		Properties prop = new Properties();
		prop.put(SP.mailServer, SP.mailServerIP);

		javax.mail.Session session = javax.mail.Session.getDefaultInstance(prop, null);
		MimeMessage msg = new MimeMessage(session);

		msg.setSubject(subject);
		InternetAddress from = new InternetAddress(fromAddr);
		from.setPersonal(fromAddrName, SP.mailLocale);
		msg.setFrom(from);

		InternetAddress ia = new InternetAddress();

		String ml = "";

		// 正本收件人
		for (int i = 0; i < to.size(); i++) {
			if (!ml.contains(to.get(i)[1])) {
				System.out.println("正本收件人:" + to.get(i)[0]);
				ia.setPersonal(to.get(i)[0], SP.mailLocale);
				ia.setAddress(to.get(i)[1]);
				msg.addRecipient(RecipientType.TO, ia);
				ml += to.get(i)[1] + "&&";
			}
		}
		// 副本收件人
		for (int i = 0; i < cc.size(); i++) {
			if (!ml.contains(cc.get(i)[1])) {
				System.out.println("副本收件人:" + cc.get(i)[0]);
				ia.setPersonal(cc.get(i)[0], SP.mailLocale);
				ia.setAddress(cc.get(i)[1]);
				msg.addRecipient(RecipientType.CC, ia);
				ml += cc.get(i)[1] + "&&";
			}
		}

		// 密件副本收件人
		for (int i = 0; i < bcc.size(); i++) {
			if (!ml.contains(bcc.get(i)[1])) {
				ia.setPersonal(bcc.get(i)[0], SP.mailLocale);
				ia.setAddress(bcc.get(i)[1]);
				msg.addRecipient(RecipientType.BCC, ia);
				ml += bcc.get(i)[1] + "&&";
			}
		}
		
		
		// 確認存在收信者，再寄信
		if (msg.getAllRecipients() != null &&msg.getAllRecipients().length > 0) {
			Multipart mp = new MimeMultipart();
			MimeBodyPart mbp1 = new MimeBodyPart();
			MimeBodyPart mbpTxt = new MimeBodyPart();
			mbpTxt.setContent(msgBody, "text/html;charset=UTF-8");
			
			FileDataSource source = new FileDataSource(file);
			mbp1.setDataHandler(new DataHandler(source));
			mbp1.setFileName(file.getName());
			
			mp.addBodyPart(mbpTxt);
			mp.addBodyPart(mbp1);
			
			msg.setContent(mp);
			Transport.send(msg);
			System.out.println("收件人清單長度："+msg.getAllRecipients().length);
		} else {
			System.out.println("沒有找到任何收件人耶");
		}
	}
	/**
	 * @param jobName
	 * @return
	 * @throws Exception
	 *             <p>
	 *             Commented on:2014/5/16 上午10:10:31<br>
	 *             Created by: Arthur
	 */
	public static int triggerJenkinsBuild(String jobName) throws Exception {

		String urlStr = SP.jenkinsUrl + "job/" + jobName + "/build?token="
				+ jobName;
		// 連線取得資料
		PostMethod postMethod = new PostMethod(urlStr);
		postMethod.setRequestHeader("Content-type",
				"application/xml; charset=UTF-8");

		HttpClient client = new HttpClient();
		client.getParams().setAuthenticationPreemptive(true);
		client.getState().setCredentials(AuthScope.ANY,
				new UsernamePasswordCredentials(SP.jenkinsId, SP.jenkinsPwd));
		int returnCode = client.executeMethod(postMethod);
		return returnCode;
	}

	/**
	 * 檢查是否 Request.getparameter所讀的參數是存在的
	 * 
	 * @param editType
	 * @return
	 */
	private static boolean isReqExist(String editType) {
		boolean isRequestExist = false;
		if (editType != null && !editType.isEmpty()) {
			isRequestExist = true;
		}
		return isRequestExist;
	}

	/**
	 * 刪除已完成的待辦事項，將其加入待辦清單
	 * 
	 * @param toDoListId
	 *            待辦事項的編號
	 * @param applierId
	 *            申請該事項的人員ID
	 *            <p>
	 *            Commented on:2014/3/12 上午9:38:55<br>
	 *            Created by: Arthur
	 */
	public static void completedList(int toDoListId, String applierId) {
		// 加入完成清單，因為這個SQL中，是用 SELECT todolist組成，所以必須在下個刪除動作之前，否則會沒東西INSERT
		String sql = "INSERT INTO completedlist (completedId,subsysId,roleId,dataType,masterId,completorId,cDT,eDT) SELECT todolistId,subsysId,roleId,dataType,masterId,'"
				+ applierId
				+ "',cDT,'"
				+ cDT().toString()
				+ "' FROM todolist WHERE todolistId=?";
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, toDoListId);
			ps.execute();
			if (ps != null) {
				ps.close();
			}

			// 刪除toDoList項目
			sql = "DELETE FROM todolist WHERE toDoListId=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, toDoListId);
			ps.execute();
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 取得指定員工的Email Addr，陣列0是名字，陣列1是email addr
	 * 
	 * @param empId
	 * @return
	 * @throws Exception
	 * <br>
	 *             創建時間：2013/10/8 下午1:40:07<br>
	 *             創建人員：郭啟揚 (842297)
	 */
	public static String[] getEmailAddr(String empId) throws Exception {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String[] emailAddr = new String[2];

		try {
			conn = getConn();
			ps = conn
					.prepareStatement("SELECT empName,empEmail FROM emps WHERE empId=?");
			ps.setString(1, empId);
			rs = ps.executeQuery();
			rs.next();
			emailAddr[0] = rs.getString("empName");
			emailAddr[1] = rs.getString("empEmail");

		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

		return emailAddr;
	}

	private static boolean isNumaric(String str) {
		try {
			Double.parseDouble(str);
		} catch (NumberFormatException nfe) {
			return false;
		}
		return true;
	}

	public static int getParNumberFromRequest(String str) {
		int res = 0;
		if (isReqExist(str)) {
			if (isNumaric(str)) {
				res = Integer.parseInt(str);
			}
		}
		return res;
	}

	public static ResultPack setResultPack(ResultPack rp, Exception e) {
		rp.setSuccess(false);
		rp.setShortErrorMessage(e.toString());
		rp.setLongErrorMessage(errProcess(e));
		return rp;
	}

	/**
	 * 修改繳交記錄的執行狀態
	 * 
	 * @param chkInId
	 * @param chkStatusId
	 * @throws Exception
	 * <br>
	 *             創建時間：2013/11/22 下午1:46:44<br>
	 *             創建人員：郭啟揚 (842297)
	 */
	public static void updateCHKINstatus(int chkInId, int chkStatusId) throws Exception {
		Connection conn = getConn();
		PreparedStatement ps = null;

		String sql = "UPDATE chkinmain SET chkInStatusId=? WHERE chkInMainId=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, chkStatusId);
		ps.setInt(2, chkInId);
		ps.execute();
		ps.close();
		conn.close();
	}
	
	public static void updateQuartzStatus(int quartzJobId, int statusId) throws Exception {
		Connection conn = getConn();
		PreparedStatement ps = null;

		String sql = "UPDATE quartzjob SET status=? WHERE quartzJobId=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, statusId);
		ps.setInt(2, quartzJobId);
		ps.execute();
		ps.close();
		conn.close();
	}

	public static long getSVNver(String url, String usr, String pwd)
			throws Exception {
		DAVRepositoryFactory.setup();
		SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(SVNEncodingUtil.uriEncode(url)));
		ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr, pwd);
		repo.setAuthenticationManager(authManager);
		SVNDirEntry entry = repo.info(".", -1);
		repo.closeSession();
		return entry.getRevision();
	}

	private static File saveToFile(File storedFile, Document document) {
		if (storedFile.exists())
			storedFile.delete();

		FileOutputStream fos = null;
		OutputStreamWriter osw = null;
		XMLWriter writer = null;
		try {
			storedFile.createNewFile();

			OutputFormat format = OutputFormat.createPrettyPrint();
			format.setEncoding("utf-8");
			fos = new FileOutputStream(storedFile);
			osw = new OutputStreamWriter(fos, Charset.forName("utf-8"));
			writer = new XMLWriter(osw, format);
			writer.write(document);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (writer != null) {
					writer.close();
				}
				if (osw != null) {
					osw.close();
				}
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return storedFile;
	}
	
	public static void updateJenkinsJobPostBuildURL(String committer,String modId, int chkInId,String subsysId, boolean isOL) throws Exception {

		InputStream ins = null;
		File tmpFile = null;
		FileInputStream fis = null;

		String urlStr = SP.jenkinsUrl + "job/MR_" + modId + "/config.xml";
		if (isOL) {
			urlStr = SP.jenkinsUrl + "job/OL_" + modId + "/config.xml";
		}

		String postBuildURL_SUCCESS = SP.postBuildAPserverHost + "PostAction?modId="+ modId + "&committer=" + committer+ "&chkInId=" + chkInId+ "&subsysId=" + subsysId+ "&isOL=" + isOL;
		String postBuildURL_FAIL = SP.postBuildAPserverHost + "PostActionFail?modId="+ modId + "&chkInId=" + chkInId+ "&subsysId=" + subsysId+"&isOL=" + isOL;

		Map<String, Object> map = new HashMap<String, Object>();
		try {
			// 連線取得資料
			ins = URLConnectionUtil.doGet(urlStr,true,SP.jenkinsId,SP.jenkinsPwd,map);
			SAXReader reader = new SAXReader();

			Document document = reader.read(ins);
			Element ele = document.getRootElement().element("postbuilders");
			Iterator ite = ele.elementIterator();
			Element eleSuccess = (Element)ite.next();
			Element eleFail = (Element)ite.next();
			eleSuccess.element("buildStep").element("url").setText(postBuildURL_SUCCESS);
			eleFail.element("buildStep").element("url").setText(postBuildURL_FAIL);
			
			PostMethod postMethod = new PostMethod(urlStr);
			postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
			tmpFile = new File("tmp" + System.currentTimeMillis() + ".xml");
			fis = new FileInputStream(saveToFile(tmpFile, document));
			postMethod.setRequestBody(fis);

			HttpClient client = new HttpClient();
			client.getParams().setAuthenticationPreemptive(true);
			client.getState().setCredentials(AuthScope.ANY, new UsernamePasswordCredentials(SP.jenkinsId,SP.jenkinsPwd));
			client.executeMethod(postMethod);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ins != null) { ins.close(); }
			if (fis != null) { fis.close(); }
			if (tmpFile.exists()) { tmpFile.delete(); }
		}

	}
	
	/**
	 * 查詢指定子系統，指定是MR還是OL，其所記錄的連線資訊 index 0,1,2 分別是 連線位址、登入帳號、登入密碼 type 1 是 CM MR
	 * 原始碼區, <br>
	 * type 2 是 CM OL 原始碼區, <br>
	 * type 3 是 AP MR 原始碼區, <br>
	 * type 4 是 AP OL 原始碼區, <br>
	 * type 5 是 AP 取得CM給的原始碼, <br>
	 * type 6 是 CM MR 部署檔區, <br>
	 * type 7 是 CM OL 部署檔區 <br>
	 * <br>
	 * 創建時間：2013/9/25 下午8:08:53<br>
	 * 創建人員：郭啟揚 (842297)
	 * 
	 * 
	 */
	public static ArrayList<String> getSVN4SubsysConnInfo(String subsysId,int svnTypeId) throws Exception {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<String> connSVNInfo = new ArrayList<String>();
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT svnUrl,svnUsr,svnPwd FROM mpsubsys2svn WHERE subsysId=? AND svnTypeId=?");
			ps.setString(1, subsysId);
			ps.setInt(2, svnTypeId);
			rs = ps.executeQuery();
			if( rs.next() ) {
				connSVNInfo.add(rs.getString("svnUrl"));
				connSVNInfo.add(rs.getString("svnUsr"));
				connSVNInfo.add(rs.getString("svnPwd"));	
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) { rs.close(); }
			if (ps != null) { ps.close(); }
			if (conn != null) { conn.close(); }
		}

		return connSVNInfo;
	}

	/**
	 * 直接將日期轉為前端UI偏好的排版格式輸出(這裡所為的偏好是by專案不同的) <br>
	 * 創建時間：2013/9/27 上午10:06:34<br>
	 * 創建人員：郭啟揚 (842297)
	 */
	public static String changeTimeFormatUI(Date dt) {
		return new java.text.SimpleDateFormat("yyyy-MM-dd").format(dt) + "<br>"
				+ new java.text.SimpleDateFormat("HH:mm:ss").format(dt);
	}

	/**
	 * @param subsysId	子系統
	 * @param status	需求的狀態
	 * @return
	 * @throws Exception<p>
	 * Commented on:2014/5/20 上午8:59:21<br>
	 * Created by:  Arthur
	 */
	public static ArrayList<S5> getReqs(String subsysId,ReqStatus status) throws Exception {
		String sql = "SELECT t1.reqSID,t1.reqDesc,t1.reqId,t1.cDT,t2.empName FROM reqs t1,emps t2 WHERE t1.creatorId=t2.empId AND t1.subsysId=? AND t1.status=? ORDER BY t1.cDT DESC";
		ArrayList<S5> reqsINsubsys = new ArrayList<S5>();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, subsysId);
			ps.setInt(2, status.getValue() );
			rs = ps.executeQuery();
			while (rs.next()) {
				S5 reqInfo = new S5();
				reqInfo.setS1(rs.getString("reqDesc")); // 需求描述
				reqInfo.setS2(String.valueOf(rs.getInt("reqId"))); // 需求ID
				reqInfo.setS3(changeTimeFormatUI(rs.getTimestamp("cDT"))); // 需求創建日期
				reqInfo.setS4(rs.getString("empName")); // 創建人員
				reqInfo.setS5(rs.getString("reqSID"));
				reqsINsubsys.add(reqInfo);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) { rs.close(); }
			if (ps != null) { ps.close(); }
			if (conn != null) { conn.close(); }
		}

		return reqsINsubsys;
	}

	/**
	 * 測試欲連線的SVN，測試是否能正常連線
	 * 
	 * @param url
	 *            連線的SVN主機位址
	 * @param usr
	 *            連線帳號
	 * @param pwd
	 *            連線密碼
	 * @return 0 尚未測試 2 主機不能連 3 帳密不通 4 連線正常 (1已挪做他用) <br>
	 *         創建時間：2013/9/26 上午10:45:00<br>
	 *         創建人員：郭啟揚 (842297)
	 * @throws SVNException
	 */
	public static int chkSVNconnection(String url, String usr, String pwd)
			throws SVNException {
		int resSVN4subsys = 0;

		SVNRepository repo = SVNRepositoryFactory.create(SVNURL
				.parseURIEncoded(url));
		ISVNAuthenticationManager authManager = SVNWCUtil
				.createDefaultAuthenticationManager(usr, pwd);
		repo.setAuthenticationManager(authManager);

		try {
			repo.getRepositoryPath(""); // 執行查詢相關路徑位址，以驗證連結成功

			// 能執行到以下這行表示連線成功
			resSVN4subsys = 4;
		} catch (SVNException e) {
			if (e.toString().contains("E170001")) {
				resSVN4subsys = 3; // 帳密不通
			} else if (e.toString().contains("E175002")) {
				resSVN4subsys = 2; // 主機連線失敗
			}
		} finally {
			if (repo != null) {
				repo.closeSession();
			}
		}

		return resSVN4subsys;
	}

	public static String errProcess(Exception e) {
		String mes = e.toString() + "\n\n";
		for (int i = 0; i < e.getStackTrace().length; i++) {
			mes += "  File Name:" + e.getStackTrace()[i].getFileName() + "\n";
			mes += "Line Number:" + e.getStackTrace()[i].getLineNumber() + "\n";
		}
		return mes;
	}

	/**
	 * 取得本系統在系統註冊裡的ID號 <br>
	 * 創建時間：2013/9/30 上午10:29:27<br>
	 * 創建人員：郭啟揚 (842297)
	 */
	public static int getLocalSysemId() throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int sysSN = 0;

		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT sysSN FROM envsystemlist WHERE isLocalSys='t'");
			rs = ps.executeQuery();
			rs.next();
			sysSN = rs.getInt("sysSN");
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) { rs.close(); }
			if (ps != null) { ps.close(); }
			if (conn != null) { conn.close(); }
		}

		return sysSN;
	}

	/**
	 * @param dataType 待辦事項的類型
	 * @return 該筆待辦事項的中文描述 <br>
	 *         創建時間：2013/10/7 下午2:27:12<br>
	 *         創建人員：郭啟揚 (842297)
	 */
	public static String getToDoListDataTypeDesc(int dataType) {
		String res = "";

		if (dataType == 1) { // 變更申請 (新增模組)
			res = "申請新增模組";
		} else if (dataType == 2) { // 繳交
			res = "申請繳交";
		} else if (dataType == 3) { // 等待安排部署時間
			res = "安排部署時間";
		} else if (dataType == 4) { // 審核排程繳交原始碼
			res = "新增 排程繳交原始碼";
		} else if (dataType == 5) { // 審核排程繳交原始碼
			res = "修改 排程繳交原始碼";
		}

		return res;			
	}

	/**
	 * 將日期轉換為"預設的格式"輸出 <br>
	 * 創建時間：2013/9/27 上午10:06:07<br>
	 * 創建人員：郭啟揚 (842297)
	 */
	public static String changeTimeFormat(Date dt) {
		return new java.text.SimpleDateFormat(SP.dateFormat).format(dt);
	}

	public static String errLog(Exception e) {
		String mes = errProcess(e);
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		out.println(mes);
		try {
			conn = getConn();
			ps = conn.prepareStatement("INSERT INTO errmes (errMes,createdDateTime) VALUES (?,?)");
			ps.setString(1, mes);
			ps.setTimestamp(2, cDT());
			ps.execute();
		} catch (Exception e1) {
			out.println(errProcess(e1));
		} finally {
			try {
				if (rs != null) { rs.close(); }
				if (ps != null) { ps.close(); }
				if (conn != null) { conn.close(); }
			} catch (Exception e1) { out.println(errProcess(e1)); }
		}
		return mes;
	}
	
	public static String errLog(Exception e,String para) {
		String mes = errProcess(e);
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		out.println(mes);
		try {
			conn = getConn();
			ps = conn.prepareStatement("INSERT INTO errmes (errMes,createdDateTime,des) VALUES (?,?,?)");
			ps.setString(1, mes);
			ps.setTimestamp(2, cDT());
			ps.setString(3, para);
			ps.execute();
		} catch (Exception e1) {
			out.println(errProcess(e1));
		} finally {
			try {
				if (rs != null) { rs.close(); }
				if (ps != null) { ps.close(); }
				if (conn != null) { conn.close(); }
			} catch (Exception e1) { out.println(errProcess(e1)); }
		}
		return mes;
	}

	/**
	 * 取得資料庫連線
	 */
	public static Connection getConn() {
		Context ctx = null;
		Connection conn = null;
		try {
			ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup(JNDINAME);
			conn = ds.getConnection();
		} catch (Exception e) {
			out.println("建立連線時發生錯誤" + e.toString());
		} finally{
			try {
				if( ctx != null ) { ctx.close(); }	
			} catch(Exception e) { e.printStackTrace(); }
		}
		return conn;
	}

	public static String getSysParameter(String pn) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String res = "";
		try {
			conn = getConn();
			ps = conn
					.prepareStatement("SELECT paraVal FROM configParameter WHERE paraName=?");
			ps.setString(1, pn);
			rs = ps.executeQuery();
			rs.next();
			res = rs.getString("paraVal");
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

		return res;
	}
	
	/**
	 * 建立與JBOSS的連線
	 *	@param defaultHost
	 *	@param defaultPort
	 *	@param username
	 *	@param password
	 *	@return
	 * @throws Exception 
	 */ 
	public static CommandContext initCommandContext(String defaultHost,
			int defaultPort, String username, char[] password)
			throws Exception {
		
		CommandContext cmdCtx = null;
		try {
			
			cmdCtx = CommandContextFactory.getInstance().newCommandContext(defaultHost, defaultPort, username, password);
			cmdCtx.connectController();
			cmdCtx.handle("connect");	
		} catch(Exception e) {
			errLog(e);
			throw e;
		}
		 

		return cmdCtx;
	}
	
	
	/** 產生待辦事項
	 * @param subsysId	子系統ID
	 * @param masterId	主檔ID
	 * @param empId		發起這件事的人的ID
	 * @param dataType	待辦事項的型態
	 * @param roleId	角色類型
	 */
	public static int genToDoList(String subsysId,String masterId,String empId,ToDoType tdt, Roles roleType ) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int toDoListId =0;
		try {
			conn = getConn();
			String sql="INSERT INTO todolist (subsysId,dataType,masterId,roleId,cDT,creatorId) VALUES (?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS );
			ps.setString(1, subsysId );
			ps.setInt(2, tdt.getValue() );				//主檔類型，1表示這個ID是來自變更申請
			ps.setString(3, masterId);			//主檔ID
			ps.setInt(4, roleType.getValue() );				//角色ID
			ps.setTimestamp(5, cDT());
			ps.setString(6, empId);
			ps.executeUpdate();
			
			rs = ps.getGeneratedKeys();
			rs.next();
			toDoListId = rs.getInt(1);
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return toDoListId;
	}

	/**
	 * 取得SVN CM執行檔區的連線帳密
	 * 
	 * @param subsysId
	 * @param isOL
	 * @param isCount 是否將Count數加一
	 * @return<p> Commented on:2014/5/2 下午2:02:18<br>
	 *            Created by: Arthur
	 */
	public static String[] getEXEsvnLoginInfo(String subsysId, boolean isOL, boolean isCount) {

		String[] svnLoginInfo = new String[5];
		String[] svnOSprocessInfo = new String[4]; // 處理SVN 在OS上用

		int count = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Session session = null;
		Channel channel = null;
		String sql = "";
		try {
			conn = Widgets.getConn();

			// 取得作用中的次數，並記錄連線資訊
			sql = "SELECT url,id,pwd,path,svnExeRepoSN,count FROM svnExeRepos WHERE subsysId=? AND isOL=? AND isActivated='t'";
			ps = conn.prepareStatement(sql);
			ps.setString(1, subsysId);
			ps.setString(2, isOL ? "t" : "f");
			rs = ps.executeQuery();
			rs.next();
			for (int i = 0; i < 5; i++) {
				svnLoginInfo[i] = rs.getString(i + 1);
			}
			int oriSvnExeReposSN = rs.getInt("svnExeRepoSN");
			count = rs.getInt("count");

			// 取得標準次數
			if (rs != null) { rs.close(); }
			if (ps != null) { ps.close(); }

			// 若使用次數已大於等於預設的次數，則要更換設定的svn
			if (count >= SP.svnCount) {
				/**
				 * 取得未作用中，執行時間最舊的SVN，OS連線資訊 將此子系統、isOL下的 SVN 都改為 非作用中 重建其repo 將其
				 * 執行時間寫入，執行次數歸0，改為作用中
				 **/

				// 將此SVN 執行時間寫入，執行次數歸0，並改為作用中
				sql = "UPDATE svnExeRepos SET isActivated='f' WHERE svnExeRepoSN=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, oriSvnExeReposSN);
				ps.executeUpdate();

				sql = "SELECT url,id,pwd,path,svnExeRepoSN,osip,osId,osPwd,repoPath FROM svnExeRepos WHERE subsysId=? AND isOL=? AND isActivated='f' ORDER BY lastDT ASC,count ASC";
				ps = conn.prepareStatement(sql);
				ps.setString(1, subsysId);
				ps.setString(2, isOL ? "t" : "f");
				rs = ps.executeQuery();

				if (rs.next()) { // 因為要取最舊的，故用if就夠了
					for (int i = 0; i < 5; i++) {
						svnLoginInfo[i] = rs.getString(i + 1);
					}
					for (int i = 0; i < 4; i++) {
						svnOSprocessInfo[i] = rs.getString(i + 6);
					}
				}
				int svnExeRepoSN = rs.getInt("svnExeRepoSN");

				if (ps != null) { ps.close(); }

				// 開始重建此Repository
				JSch jsch = new JSch();
				JSch.setConfig("StrictHostKeyChecking", "no");
				session = jsch.getSession(svnOSprocessInfo[1],svnOSprocessInfo[0], 22);
				session.setPassword(svnOSprocessInfo[2]);
				session.connect();
				String command = "rm -rf " + svnOSprocessInfo[3] + ";"+
								 "svnadmin create " + svnOSprocessInfo[3]+ ";"+
								 "chown apache.apache " + svnOSprocessInfo[3] + " -R";
				channel = session.openChannel("exec");
				((ChannelExec) channel).setCommand(command);
				channel.connect();
				if (channel != null) {
					if (channel.isConnected()) {
						channel.disconnect();
					}
				}
				if (session != null) {
					if (session.isConnected()) {
						session.disconnect();
					}
				}

				// 將所有的 repo都改為非用中
				sql = "UPDATE svnExeRepos SET isActivated='f' WHERE subsysId=? AND isOL=?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, subsysId);
				ps.setString(2, isOL ? "t" : "f");
				ps.executeUpdate();

				// 將此SVN 執行時間寫入，執行次數歸1，並改為作用中
				sql = "UPDATE svnExeRepos SET isActivated='t',count=0,lastDT=? WHERE svnExeRepoSN=?";
				ps = conn.prepareStatement(sql);
				ps.setTimestamp(1, Widgets.cDT());
				ps.setInt(2, svnExeRepoSN);
				ps.executeUpdate();

				try {
					System.out.println("等待10秒");
					Thread.sleep(10000);
				} catch (InterruptedException ie) {
					ie.printStackTrace();
				}

			} else {
				if( isCount ) {
					// 將作用中的SVN 使用次數加一
					sql = "UPDATE svnExeRepos SET count=? WHERE isActivated='t' AND subsysId=? AND isOL=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, count+1);
					ps.setString(2, subsysId);
					ps.setString(3, isOL ? "t" : "f");
					ps.executeUpdate();	
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) { rs.close(); }
				if (ps != null) { ps.close(); }
				if (conn != null) { conn.close(); }
				if (channel != null) { if (channel.isConnected()) { channel.disconnect(); } }
				if (session != null) { if (session.isConnected()) { session.disconnect(); } }
			} catch (Exception e) { }
		}

		return svnLoginInfo;
	}
	
	public static String[] getEXEsvnLoginInfo(int svnExeRepoSN) {

		String[] svnLoginInfo = new String[4];

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		try {
			conn = Widgets.getConn();

			// 取得作用中的次數，並記錄連線資訊
			sql = "SELECT url,id,pwd,path FROM svnExeRepos WHERE svnExeRepoSN=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, svnExeRepoSN);
			rs = ps.executeQuery();
			rs.next();
			for (int i = 0; i < 4; i++) {
				svnLoginInfo[i] = rs.getString(i + 1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) { rs.close(); }
				if (ps != null) { ps.close(); }
				if (conn != null) { conn.close(); }
			} catch (Exception e) { }
		}

		return svnLoginInfo;
	}
	

	public static String getEmpName(String empId) throws Exception {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String empName = "";

		try {
			conn = getConn();
			ps = conn
					.prepareStatement("SELECT empName FROM emps WHERE empId=?");
			ps.setString(1, empId);
			rs = ps.executeQuery();
			rs.next();
			empName = rs.getString("empName");
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

		return empName;
	}
	
	public static long checkInFiles2SVN(String url, String id, String pwd,
			String path, List<S2> modFolderOnOS) {
		long reversion = 0L;
		SVNDeltaGenerator deltaGenerator = new SVNDeltaGenerator();

		SVNRepository repo = null;
		ISVNEditor editor = null;
		SVNCommitInfo info = null;

		JSch jsch = new JSch();
		JSch.setConfig("StrictHostKeyChecking", "no");
		Session session = null;
		ChannelSftp sftp = null;
		InputStream ins = null;

		
		
		
		String ftpIP = "10.144.97.99";
		String ftpId = "Arthur";
		String ftpPwd = "arthur7382";
		
		
		try {
			//FTP連線
			session = jsch.getSession(ftpId, ftpIP);
			session.setPassword(ftpPwd);
			session.connect();
			sftp = (ChannelSftp) session.openChannel("sftp");
			sftp.connect(); 
			
			//SVN連線
			repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
			ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(id, pwd);
			repo.setAuthenticationManager(authManager);
			editor = repo.getCommitEditor(null, new CommitMediator());
			editor.openRoot(-1);
			editor.openDir(path, -1);
			for(int i=0; i<modFolderOnOS.size();i++) {
				String exeFile = modFolderOnOS.get(i).getS1();
				String folderOnOS = modFolderOnOS.get(i).getS2();
				String ftpAbsolutePath = "/opt/jenkins/" + folderOnOS + "/workspace/target";
				
				sftp.cd("/"); // 切到根目錄
				sftp.cd(ftpAbsolutePath); // 切換到指定
				ins = sftp.get(exeFile);
				
				try {
					editor.addFile(exeFile, null, -1);
				} catch (Exception e) {
					editor.openFile(exeFile, -1);
				}

				editor.applyTextDelta(exeFile, null);
				String baseChecksum = deltaGenerator.sendDelta(exeFile,ins, editor, true);
				editor.textDeltaEnd(exeFile);
				editor.closeFile(exeFile, baseChecksum);
				editor.closeDir();
				if ( ins != null) { ins.close(); }
			}
			info = editor.closeEdit(); // 本次提交結束
			reversion = info.getNewRevision();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if ( sftp != null) { if (sftp.isConnected()) { sftp.disconnect(); } }
				if ( session != null) { if (session.isConnected()) { session.disconnect(); } }
				if ( ins != null) { ins.close(); }
				if ( repo != null) { repo.closeSession(); }
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return reversion;
	}

	public static long checkInOneFile2SVN(String url, String id, String pwd,
			String path, String fileName, String modId) {
		long reversion = 0L;
		SVNDeltaGenerator deltaGenerator = new SVNDeltaGenerator();

		SVNRepository repo = null;
		ISVNEditor editor = null;
		SVNCommitInfo info = null;

		JSch jsch = new JSch();
		JSch.setConfig("StrictHostKeyChecking", "no");
		Session session = null;
		ChannelSftp sftp = null;
		InputStream ins = null;

		String ftpIP = "10.144.97.99";
		String ftpId = "Arthur";
		String ftpPwd = "arthur7382";
		String ftpAbsolutePath = "/opt/jenkins/" + modId + "/workspace/target";

		try {
			session = jsch.getSession(ftpId, ftpIP);
			session.setPassword(ftpPwd);
			session.connect();
			sftp = (ChannelSftp) session.openChannel("sftp");
			sftp.connect(); // 連線
			sftp.cd("/"); // 切到根目錄
			sftp.cd(ftpAbsolutePath); // 切換到指定
			ins = sftp.get(fileName);

			repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
			ISVNAuthenticationManager authManager = SVNWCUtil
					.createDefaultAuthenticationManager(id, pwd);
			repo.setAuthenticationManager(authManager);

			editor = repo.getCommitEditor(null, new CommitMediator());
			editor.openRoot(-1);
			editor.openDir(path, -1);
			try {
				editor.addFile(fileName, null, -1);
			} catch (Exception e) {
				editor.openFile(fileName, -1);
			}

			editor.applyTextDelta(fileName, null);
			String baseChecksum = deltaGenerator.sendDelta(fileName, ins,
					editor, true);
			editor.textDeltaEnd(fileName);
			editor.closeFile(fileName, baseChecksum);
			editor.closeDir();
			info = editor.closeEdit(); // 本次提交結束
			reversion = info.getNewRevision();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (sftp != null) { if (sftp.isConnected()) { sftp.disconnect(); } }
				if (session != null) { if (session.isConnected()) { session.disconnect(); } }
				if (ins != null) { ins.close(); }
				if (repo != null) { repo.closeSession(); }
			} catch (Exception e) { e.printStackTrace(); }
		}
		return reversion;
	}

	public static String getJenkinsUrl(String modId, int jobSN, String dataType) {
		String url = SP.jenkinsUrl;
		String jobId = "OL_" + modId;
		if (jobSN == 0) {
			url += "job/" + jobId + "/api/" + dataType;
		} else {
			url += "job/" + jobId + "/" + jobSN + "/api/" + dataType;
		}
		return url;
	}
	public static String getJenkinsConsoleMsg(String modId, int jobSN) {
		String url = SP.jenkinsUrl;
		String jobId = "OL_" + modId;
		url += "job/" + jobId + "/"+jobSN+"/logText/progressiveText?start=0";
		return url;
		
	}

	public static Timestamp cDT() {
		return Timestamp.valueOf(new SimpleDateFormat(SP.dateFormat).format(new Date()));
	}

	public static String readUrl(String urlString) throws Exception {
		InputStream ins = null;
		Scanner scannerA = null;
		Scanner scannerB = null;
		String tmp = "";
		try {
			ins = new URL(urlString).openStream();
			scannerA = new Scanner(ins);
			scannerB = scannerA.useDelimiter("\\A");
		    tmp = scannerB.hasNext() ? scannerB.next() : ""; 
		} finally {
			if ( scannerA != null) { scannerA.close(); }
			if ( scannerB != null) { scannerB.close(); }
			if (ins != null) { ins.close(); }
		}
		return tmp;
	}
	
	public static File string2File(String str,String fileName) throws Exception {
		InputStream ins = null;
		OutputStream ops = null;
		File file = new File( fileName );
		try {
			ins = new ByteArrayInputStream(str.getBytes());
			ops = new FileOutputStream( file );
	 
			int read = 0;
			byte[] bytes = new byte[1024];
	 
			while ((read = ins.read(bytes)) != -1) {
				ops.write(bytes, 0, read);
			}
		} finally {
			if (ins != null) {ins.close();}
			if (ops != null) { ops.close(); }
		}
		return file;
	}
	

	public String getSVN2fileTreeJSON(String url, String usr, String pwd,long ver, boolean is4Dep) {
		
		SVNClientManager manager;
		SVNURL repositoryBaseUrl;

		manager = SVNClientManager.newInstance(new DefaultSVNOptions(), usr,pwd); // 如果需要用戶名密碼
		try {
			repositoryBaseUrl = SVNURL.parseURIEncoded(url); // 傳入svn地址
			SVNLogClient logClient = manager.getLogClient();
			ISVNDirEntryHandler handler = null;
			if (is4Dep ) {
				
				if( SP.isNeedQAqualified ) {
					handler = new File4DepEntryHandler();
				} else {
					handler = new DepFileEntryHandler();
				}
				logClient.doList(repositoryBaseUrl, SVNRevision.HEAD, SVNRevision.HEAD, false, SVNDepth.INFINITY, SVNDirEntry.DIRENT_ALL, handler);
			} else {
				handler = new FileEntryHandler();
				logClient.doList(repositoryBaseUrl, SVNRevision.create(ver), SVNRevision.create(ver), false, SVNDepth.IMMEDIATES, SVNDirEntry.DIRENT_KIND, handler);
				//直接加入trunk，因為目前僅掃描 branch
				res += "{'id':'trunk','pId':'rroott','open':'true','name':'trunk','icon':'./pic/folder.gif'},";	
			}
			
			
			// 列出當前svn地址的目錄，對每個文件進行處理
			res = res.substring(0, res.length() - 1) + "]";
		} catch (SVNException e) {
			errLog(e);
		} finally {
			manager.dispose();
		}

		return res;
	}
	
	//不論是否有過QA區的內容都會回傳
	class DepFileEntryHandler implements ISVNDirEntryHandler {
		public void handleDirEntry(SVNDirEntry entry) throws SVNException {
			String parentDir = null;
			String path = entry.getRelativePath();
			
			if (!path.equalsIgnoreCase("")) {
				if (path.contains("/")) {
					parentDir= path.substring(0,path.lastIndexOf("/"));
				} else {
					parentDir = "rroott";
				}
				
				if ( entry.getKind() == SVNNodeKind.DIR ) {
					//名和值都有單引號
					res += "{'id':'"+path+"','pId':'"+parentDir+"','open':'true','name':'"+entry.getName()+"','icon':'./pic/folder.gif'},";
				} else if ( entry.getKind() == SVNNodeKind.FILE ) {
					if( entry.getName().contains(".war") || entry.getName().contains(".ear") || entry.getName().contains(".jar")) {
						res += "{'id':'"+path+"','pId':'"+parentDir+"','name':'"+entry.getName()+"','icon':'./pic/file.png'},";	
					}
				}
			}
		}
	}
	
	//會比對有部署過到QA區的內容才會回傳
	class File4DepEntryHandler implements ISVNDirEntryHandler {
		public void handleDirEntry(SVNDirEntry entry) throws SVNException {
			String path = entry.getRelativePath();
			if (!"".equalsIgnoreCase( path.trim() )) {
				Connection conn = null;
				PreparedStatement ps = null;
				ResultSet rs = null;
				String sql = "SELECT COUNT(*) FROM depmemphisdetail WHERE isDep='t' AND isSuccess='t' AND fileName=? AND squadId='testEnvServerGroup'";
				
				try {
					conn = getConn();
					ps = conn.prepareStatement(sql);
					ps.setString(1, entry.getName());
					rs = ps.executeQuery();
					rs.next();
					if( rs.getInt(1) > 0 ) {
						res += "{'id':'" + path + "','pId':'rroott','open':'true','name':'" + entry.getName()
								+ "','icon':'./pic/file.png'},";
					}
				} catch (Exception e) {
					errLog(e);
				} finally {
					try {
						if ( rs !=null ) {rs.close();}
						if ( ps !=null ) {ps.close();}
						if ( conn !=null ) {conn.close();}	
					} catch(Exception e) {e.printStackTrace();}
					
				}
				
				
				
			}
		}
	}
	
	class FileEntryHandler implements ISVNDirEntryHandler {
		public void handleDirEntry(SVNDirEntry entry) throws SVNException {
			String path = entry.getRelativePath();
			if (!"".equalsIgnoreCase( path.trim() )) {
				res += "{'id':'branch/" + path + "','pId':'rroott','open':'true','name':'branch/" + entry.getName()
						+ "','icon':'./pic/folder.gif'},";
			}
		}
	}

	/**
	 * 計算某符號在字串中出現的次數
	 * 
	 * @param strKeywords 字串
	 * @param strkey 某符號
	 * @return<p> Commented on:2014/5/6 下午3:18:18<br>
	 *            Created by: Arthur
	 */
	public static int numberOfKeywords(String strKeywords, String strkey) {
		int spcount = 0;
		String strTmp = "";
		for (int j = 0; j < strKeywords.length(); j++) {
			strTmp = String.valueOf(strKeywords.charAt(j));
			if (strTmp.equals(strkey)) {
				spcount++;
			}
		}

		return spcount;
	}
}